import pandas as pd
from jobs.sql_mehods import MySQLHelper

# 定义SQL查询语句和数据库信息
master_db_sql = """
select * from elec_warn_records
where 
start_time >= '2024-01-09 06:00:00'
and 
start_time <= '2024-01-15 09:00:00'
and county_name !='丰台区'
and  county_name !='密云区'
"""

master_db_info = {
    "host": "mmservice-05.mysql.hotgrid.cn",
    "port": 3306,
    "user": "electricity_api_service",
    "password": "GJlfh7&#jg",
    "db": "electricity_data",
    "charset": "utf8"
}

new_db_info = {
    "host": "10.20.7.227",
    "port": 33176,
    "user": "electricity_data",
    "password": "EF2zUl1GHss3yqay",
    "db": "electricity_data",
    "charset": "utf8"
}

def main():
    # 创建数据库连接
    master_db = MySQLHelper(**master_db_info)
    new_db = MySQLHelper(**new_db_info)

    # 从master_db中获取数据
    master_data = master_db.execute_select(master_db_sql)
    master_data = pd.DataFrame(master_data)

    print(master_data)

    # 插入到new_db中，你需要创建一个适当的INSERT语句
    # 这里假设master_data有列'name'和'value'
    for _, row in master_data.iterrows():
        columns = ', '.join(row.index)
        values = ', '.join(f"'{v}'" if isinstance(v, str) else str(v) for v in row.values)
        updates = ', '.join(f"{k} = '{v}'" if isinstance(v, str) else f"{k} = {v}" for k, v in row.items())
        insert_sql = f"""
        INSERT INTO elec_warn_records ({columns}) VALUES ({values})
        ON DUPLICATE KEY UPDATE {updates}
        """
        # new_db.execute_modify(insert_sql)
        print(insert_sql)

        new_db.execute_edit(insert_sql)

if __name__=="__main__":
    main()
